"""
Case Type   : 兼容函数
Case Name   : 验证crc32()函数与各种运算符的交互使用
Create At   : 2023.09.04
Owner       : zhanghuan96
Description :
    1、创建兼容b库库并查询插件
    2、crc32()结合算术运算符使用
    3、crc32()结合比较运算符、逻辑运算符使用
    4、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
History     :modified by zhanghuan96，2023.09.04，yat执行结果的精度处理和手工执行
             结果不一致， 修改用例为py确保执行结果同手工执行结果一致
             modified by qumin20145，2024.03.04，类似select 1!这种单后置操作符语法不支持，同步优化用例
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest016(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_conv_0016'
        self.tb_name = 'tb_conv_0009'
        self.common = Common()

    def test_create_function(self):
        text = '-----step1:创建兼容b库库并查询插件;expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility ='B';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS,
                      sql_res, '兼容B库创建失败')

        sql_cmd1 = "select extname from pg_extension where extname ='dolphin';"
        self.log.info(sql_cmd1)
        sql_res1 = self.sh_primary.execut_db_sql(sql_cmd1, dbname=self.db_name)
        self.log.info(sql_res1)
        self.assertIn('dolphin', sql_res1, '查询失败')

        text = '-----step2:crc32()结合算术运算符使用;expect:成功-----'
        self.log.info(text)
        sql_cmd4 = f"select crc32(5)+3,crc32('22C')-1,crc32('21')*2," \
                   f"crc32('F')/5 result;"
        self.log.info(sql_cmd4)
        sql_res4 = self.sh_primary.execut_db_sql(sql_cmd4, dbname=self.db_name)
        self.log.info(sql_res4)
        rows, _ = self.common.parse_sql_query_res(sql_res4)
        expect = [['2226203569', '3658198198', '8504905064', '260846958.4']]
        self.assertEqual(rows, expect, '执行失败' + text)

        sql_cmd5 = f"select +crc32(11011),-crc32('td') result;"
        self.log.info(sql_cmd5)
        sql_res5 = self.sh_primary.execut_db_sql(sql_cmd5, dbname=self.db_name)
        self.log.info(sql_res5)
        rows, _ = self.common.parse_sql_query_res(sql_res5)
        expect = [['2702973943', '-1079315276']]
        self.assertEqual(rows, expect, '执行失败' + text)

        sql_cmd6 = f"select crc32('cc')%2,@(-crc32('philips')) result;"
        self.log.info(sql_cmd6)
        sql_res6 = self.sh_primary.execut_db_sql(sql_cmd6, dbname=self.db_name)
        self.log.info(sql_res6)
        rows, _ = self.common.parse_sql_query_res(sql_res6)
        expect = [['1', '3510924858']]
        self.assertEqual(rows, expect, '执行失败' + text)

        sql_cmd7 = f"select (crc32('1100'))^3,|/crc32('c'),||/crc32(28)," \
                   f"crc32('null')^(-2),crc32('c')^(0.5) result;"
        self.log.info(sql_cmd7)
        sql_res7 = self.sh_primary.execut_db_sql(sql_cmd7, dbname=self.db_name)
        self.log.info(sql_res7)
        rows, _ = self.common.parse_sql_query_res(sql_res7)
        expect = [['3051735331', '10622.8364856097', '1305.66838590457',
                   '-634125391', '10622.836485609670']]
        self.assertEqual(rows, expect, '执行失败' + text)

        sql_cmd8 = f"select (crc32('1100')-3051735320)!," \
                   f"!!(crc32('1100')-3051735320) result;"
        self.log.info(sql_cmd8)
        sql_res8 = self.sh_primary.execut_db_sql(sql_cmd8, dbname=self.db_name)
        self.log.info(sql_res8)
        self.assertIn('ERROR', sql_res8, '执行失败' + text)

        sql_cmd9 = f"select crc32(2)&0,crc32(5)|0,crc32(2)#crc32(2)," \
                   f"~crc32(8),crc32(5)<<2,crc32(5)>>2 result;"
        self.log.info(sql_cmd9)
        sql_res9 = self.sh_primary.execut_db_sql(sql_cmd9, dbname=self.db_name)
        self.log.info(sql_res9)
        rows, _ = self.common.parse_sql_query_res(sql_res9)
        expect = [
            ['0', '2226203566', '0', '-4194326292', '8904814264', '556550891']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = '-----step3:crc32()结合比较运算符、逻辑运算符使用;expect: 成功-----'
        self.log.info(text)
        sql_cmd10 = f"select crc32(1101)>3.14 and crc32('c')<=4 result;" \
                    f"select crc32('2c')<=3 or crc32('c')<>4 result;" \
                    f"select crc32(11011)=crc32(11011) result;"
        self.log.info(sql_cmd10)
        sql_res10 = self.sh_primary.execut_db_sql(sql_cmd10,
                                                  dbname=self.db_name)
        self.log.info(sql_res10)
        self.assertEqual('f', sql_res10.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('t', sql_res10.splitlines()[7].strip(), '执行失败' + text)
        self.assertEqual('t', sql_res10.splitlines()[-2].strip(),
                         '执行失败' + text)

    def tearDown(self):
        text = '-----step4:清理环境;expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS,
                      sql_res, '清理环境失败')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
